package com.codeye.office.excel;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.List;
import java.util.Map;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.codeye.audio.AudioFilePlayer;

public class ExcelWriteTools extends ExcelBase {
	
    public static void Write_rw(String finalXlsxPath, Map<String, String> resultMap, boolean isHasTitle, int keyCol, int reTryIimes){
    	reTryIimes += 1;
    	int fault_tolerant_count = 6;
    	FileOutputStream fos = null;  
    	boolean eflag = false;
    	String einfo = "";
        try {  
            File finalXlsxFile = new File(finalXlsxPath);  		// 读取Excel文档  
            Workbook workBook = getWorkbok(finalXlsxFile);
            
            Sheet sheet = workBook.getSheetAt(0);  				// sheet 对应一个工作页  
            /** 改写原有数据，除了属性列 */  
    		CellStyle cellStyle = workBook.createCellStyle();  
        	cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());  
        	cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        	Font font = workBook.createFont();
            font.setColor(IndexedColors.WHITE.getIndex());//HSSFColor.VIOLET.index //字体颜色
            cellStyle.setFont(font);
            
            int rowNumber = sheet.getLastRowNum()+1;  			// 第一行从0开始算
            int begLineNum = isHasTitle==true?1:0;
            for (int i = begLineNum; i <= rowNumber; i++) {
                Row row = sheet.getRow(i);  
                if(null != row){
                	Cell cu = row.getCell(keyCol - 1);
                	Cell cn = row.getCell(keyCol);
                	if(null == cn){
                		cn = row.createCell(keyCol);
                    	String webName = resultMap.get(cu.getStringCellValue().trim());
                    	if(null == webName){
                    		System.err.println("@@@@@@@@@@@>>>"+cu.getStringCellValue());
                    		cn.setCellStyle(cellStyle);
                        	cn.setCellValue("null");
                    	}else{
                        	if(webName.equalsIgnoreCase("unknow")){
                        		cn.setCellStyle(cellStyle);	
                        	}
                        	cn.setCellValue(webName);
                    	}
                	}else if(cn.getStringCellValue().trim().equals("") || cn.getStringCellValue().trim().equals("unknow") || cn.getStringCellValue().trim().equals("none")){
                    	String webName = resultMap.get(cu.getStringCellValue().trim());
                    	if(null == webName){
                    		System.err.println("@@@@@@@@@@@>>>"+cu.getStringCellValue());
                    		cn.setCellStyle(cellStyle);
                        	cn.setCellValue("null");
                    	}else{
                        	if(webName.equalsIgnoreCase("unknow")){
                        		cn.setCellStyle(cellStyle);	
                        	}
                        	cn.setCellValue(webName);
                    	}
                	}
                }
            }  	

            // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            fos =  new FileOutputStream(finalXlsxPath);  
            workBook.write(fos);  
            System.out.println("已添加数据行数：" + rowNumber);
        } catch (FileNotFoundException e) {  
        	eflag = true;
        	einfo = "文件不可用";
        } catch (NullPointerException e) {  
        	eflag = true;
        	einfo = "空指针";
        	e.printStackTrace();
        } catch (IOException e) {
        	eflag = true;
        	einfo = "io异常";
		} catch (Exception e) {
        	eflag = true;
        	einfo = "未知异常";
        	e.printStackTrace();
		} finally{  
            try {  
                if(fos != null){  
                    fos.flush();  
                    fos.close();  
                }  
            } catch (IOException e) {  
                e.printStackTrace();
                eflag = true;
            }  
        } 
        if(eflag){
			System.out.println("**************************************************");
			System.out.println("***************  "+einfo+" 10秒重试    **************");
			System.out.println("**************************************************");
			String sepath = System.getProperty("user.dir")+"\\src\\com\\codeye\\audio\\se\\";
        	AudioFilePlayer ap = new AudioFilePlayer();
        	if(einfo.equalsIgnoreCase("文件不可用")){
        		ap.play(sepath+"landing seq interupted.wav");
        	}else{
        		ap.play(sepath+"啊咧.mp3");
        	}
    		if(reTryIimes < fault_tolerant_count){
	    		try {
					Thread.sleep(5000);
					Write_rw(finalXlsxPath, resultMap, true, keyCol, reTryIimes);
				} catch (InterruptedException e) {
					System.out.println("*************************");
					System.out.println("***Thread.sleep(1000)****");
					System.out.println("*************************");
				}
    		}
        }
        
    }  
	
	
	
	
	
	
    public static void WriteAccess(Map<String, String> imap, String finalXlsxPath){
    	FileOutputStream fos = null;  
    	boolean eflag = false;
    	String einfo = "";
        try {  
            // 读取Excel文档  
            File finalXlsxFile = new File(finalXlsxPath);  
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页  
            Sheet sheet = workBook.getSheetAt(0);  
            /** 改写原有数据，除了属性列 */  
            int rowNumber = sheet.getLastRowNum();  // 第一行从0开始算

    		CellStyle cellStyle = workBook.createCellStyle();  
        	cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());  
        	//cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        	Font font = workBook.createFont();
            font.setColor(IndexedColors.WHITE.getIndex());//HSSFColor.VIOLET.index //字体颜色
            cellStyle.setFont(font);
            
            for (int i = 1; i <= rowNumber; i++) {
                Row row = sheet.getRow(i);  
                if(null != row){
                	Cell cu = row.getCell(3);
                	Cell cr = row.createCell(4);
                	String accessFlag = imap.get(cu.getStringCellValue());
                	if(null == accessFlag){
                		System.err.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");
                		System.err.println("@@@@@@@@@@@   "+cu.getStringCellValue()+"   @@@@@@@@@@@");
                		System.err.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");
                	}
                	if(!accessFlag.equalsIgnoreCase("ok")){
                		cr.setCellStyle(cellStyle);	
                	}
                	cr.setCellValue(accessFlag);
                }
            }  	

            // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            fos =  new FileOutputStream(finalXlsxPath);  
            workBook.write(fos);  
            System.out.println("已添加数据行数：" + rowNumber);
        } catch (FileNotFoundException e) {  // FileNotFoundException
        	eflag = true;
        	einfo = "文件不可用";
        } catch (NullPointerException e) {  // FileNotFoundException
        	eflag = true;
        	einfo = "空指针";
        } catch (IOException e) {
        	eflag = true;
        	einfo = "io异常";
		} finally{  
            try {  
                if(fos != null){  
                    fos.flush();  
                    fos.close();  
                }  
            } catch (IOException e) {  
                e.printStackTrace();
                eflag = true;
            }  
        } 
        if(eflag){
			System.out.println("**************************************************");
			System.out.println("***************  "+einfo+" 10秒重试    **************");
			System.out.println("**************************************************");
        	AudioFilePlayer ap = new AudioFilePlayer();
        	if(einfo.equalsIgnoreCase("文件不可用")){
        		ap.play("F:/KZ_Music/音效/landing seq interupted.wav");
        	}else{
        		ap.play("F:/KZ_Music/音效/not enough energy.wav");
        	}
    		
    		try {
				Thread.sleep(5000);
				WriteAccess(imap, finalXlsxPath);
			} catch (InterruptedException e) {
				System.out.println("*************************");
				System.out.println("***Thread.sleep(1000)****");
				System.out.println("*************************");
			}
        }
        
    }  
    
    
    public static void Write(List<Map<String, String>> dataList, String finalXlsxPath, boolean addFlag){  
        OutputStream out = null;  
        try {  
            // 读取Excel文档  
            File finalXlsxFile = new File(finalXlsxPath);  
            Workbook workBook = getWorkbok(finalXlsxFile);  
            // sheet 对应一个工作页  
            Sheet sheet = workBook.getSheetAt(0);  
            /** 
             * 删除原有数据，除了属性列 
             */  
            int rowNumber = sheet.getLastRowNum();  // 第一行从0开始算
            if(!addFlag){
                for (int i = 1; i <= rowNumber; i++) {
                	System.out.println(i);
                    Row row = sheet.getRow(i);  
                    if(null != row){
                    	sheet.removeRow(row);	
                    }
                }  	
                rowNumber = 0;
            }
            // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            out =  new FileOutputStream(finalXlsxPath);  
            workBook.write(out);  
            /** 
             * 往Excel中写新数据 
             */  
            for (int j = 0; j < dataList.size(); j++) {  
                // 创建一行：从第二行开始，跳过属性列  
                Row row = sheet.createRow(rowNumber + j + 1);  
                // 得到要插入的每一条记录  
                Map<String, String> dataMap = dataList.get(j);  
                String url = dataMap.get("url").toString();  
                String unm = dataMap.get("unm").toString();  
                // 在一行内循环  
                Cell c1 = row.createCell(0);  
                c1.setCellValue(url);  
                Cell c2 = row.createCell(1);  
                c2.setCellValue(unm);  
            }  
            // 创建文件输出流，准备输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效  
            out =  new FileOutputStream(finalXlsxPath);  
            workBook.write(out);  
            System.out.println("已添加数据行数：" + rowNumber);
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally{  
            try {  
                if(out != null){  
                    out.flush();  
                    out.close();  
                }  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }  
    }  
  
    public static void WriteTouch(List<String> dataList, String finalXlsxPath){
    	FileOutputStream fos = null;  
        try {  
            // 读取Excel文档  
        	
            File finalXlsxFile = new File(finalXlsxPath);  
            Workbook workBook = getWorkbok(finalXlsxFile);
            // sheet 对应一个工作页  
            Sheet sheet = workBook.getSheetAt(0);  
            /** 
             * 删除原有数据，除了属性列 
             */  
            int rowNumber = sheet.getLastRowNum();  // 第一行从0开始算

    		CellStyle cellStyle = workBook.createCellStyle();  
        	cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());  
//        	cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);  
        	Font font = workBook.createFont();
            font.setColor(IndexedColors.WHITE.getIndex());//HSSFColor.VIOLET.index //字体颜色
            cellStyle.setFont(font);
            
            for (int i = 1; i <= dataList.size(); i++) {
                Row row = sheet.getRow(i);  
                if(null != row){
                	Cell c = row.createCell(4);
                	String accessFlag = dataList.get(i-1);
                	if(!accessFlag.equalsIgnoreCase("ok")){
                    	c.setCellStyle(cellStyle);	
                	}
                	c.setCellValue(dataList.get(i-1));
                }
            }  	
            // 创建文件输出流，输出电子表格：这个必须有，否则你在sheet上做的任何操作都不会有效
            fos =  new FileOutputStream(finalXlsxPath);  
            workBook.write(fos);  
            System.out.println("已添加数据行数：" + rowNumber);
        } catch (Exception e) {  
            e.printStackTrace();  
        } finally{  
            try {  
                if(fos != null){  
                    fos.flush();  
                    fos.close();  
                }  
            } catch (IOException e) {  
                e.printStackTrace();  
            }  
        }  
    }  
}  
